﻿using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Synergy
{
    public partial class Form导出BOM: Form
    {
        public Form导出BOM()
        {
            InitializeComponent();
        }

        private void button按母件导出_Click(object sender, EventArgs e)
        {
            //将文本框按行分割
            string[] InvCodes = textBox1.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            if (InvCodes.Length == 0)
            {
                MessageBox.Show("请输入母件编码,多个编码请分多行");
                return;
            }
            DataTable dt = 按母件查();
            导出(dt);
        }

        private void button按子件导出_Click(object sender, EventArgs e)
        {
            //将文本框按行分割
            string[] InvCodes = textBox1.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            if (InvCodes.Length == 0)
            {
                MessageBox.Show("请输入母件编码,多个编码请分多行");
                return;
            }
            DataTable dt = 按子件查();
            导出(dt);
        }

        private DataTable 按母件查()
        {
            string[] InvCodes = textBox1.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            string sql = $@"select a.Version 版本号,a.VersionDesc 版本说明,c.InvCode as '母件编码',d.cInvName as '母件名称',d.cInvStd 母件规格,zj.cInvCode 子件编码,
                           zj.cInvStd 子件规格, f.OpComponentId ,f.componentId,f.BaseQtyN /f.BaseQtyD 用量,view_采购入库单价.iUnitCost 子件单价
                            from bom_bom as a
                            join bom_parent as b on a.BomId=b.BomId
                            join bas_part as c on b.ParentId=c.PartId
                            join Inventory as d on c.InvCode=d.cInvCode
                            join ComputationUnit as e ON d.cComUnitCode =e.cComUnitCode
                            join bom_opcomponent f on f.BomId = b.BomId
                            join bas_part zi on zi.PartId = f.componentId
                            join Inventory  as zj on zi.InvCode=zj.cInvCode
left join view_采购入库单价 on view_采购入库单价.cInvCode = zj.cInvCode
                            where 1=1 --and c.InvCode like 'YZ00115' 
                            and c.InvCode in ({string.Join(",", InvCodes.Select(x => $"'{x}'"))})
                            --AND zj.cInvCode  LIKE '1%'
                            order by c.InvCode";
            DataTable dt = pub.U8GetTable(sql);
            return dt;
        }

        private DataTable 按子件查()
        {
            string[] InvCodes = textBox1.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            string sql = $@"select a.Version 版本号,a.VersionDesc 版本说明,c.InvCode as '母件编码',d.cInvName as '母件名称',d.cInvStd 母件规格,zj.cInvCode 子件编码,
                           zj.cInvStd 子件规格, f.OpComponentId ,f.componentId,f.BaseQtyN /f.BaseQtyD 用量,view_采购入库单价.iUnitCost 子件单价
                            from bom_bom as a
                            join bom_parent as b on a.BomId=b.BomId
                            join bas_part as c on b.ParentId=c.PartId
                            join Inventory as d on c.InvCode=d.cInvCode
                            join ComputationUnit as e ON d.cComUnitCode =e.cComUnitCode
                            join bom_opcomponent f on f.BomId = b.BomId
                            join bas_part zi on zi.PartId = f.componentId
                            join Inventory  as zj on zi.InvCode=zj.cInvCode
                            left join view_采购入库单价 on view_采购入库单价.cInvCode = zj.cInvCode
                            where 1=1 --and c.InvCode like 'YZ00115' 
                            and zj.cInvCode in ({string.Join(",", InvCodes.Select(x => $"'{x}'"))})
                            --AND zj.cInvCode  LIKE '1%'
                            order by c.InvCode";
            DataTable dt = pub.U8GetTable(sql);
            return dt;
        }

        private void 导出(DataTable dt)
        {
            //选择路径
            //通过miniexcel导出材料计算
         
            //选择保存路径
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
            saveFileDialog.FilterIndex = 1;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.FileName = "BOM" + DateTime.Now.ToString("yyyy-MM-dd。HH-mm-ss") + "。" + DateTime.Now.ToFileTime();
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string path = saveFileDialog.FileName;
                //导出
                MiniExcel.SaveAs(path, dt);
            }

        }

        private void button导出所有_Click(object sender, EventArgs e)
        {
            string sql = "select *,view_采购入库单价.iUnitCost 子件单价 from view_BOM查询 left join view_采购入库单价 on view_采购入库单价.cInvCode = view_BOM查询.子件编码 order by 母件编码";
            DataTable dt = pub.U8GetTable(sql);
            //选择保存路径
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
            saveFileDialog.FilterIndex = 1;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.FileName = "所有BOM" + DateTime.Now.ToFileTime();
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string path = saveFileDialog.FileName;
                //导出
                MiniExcel.SaveAs(path, dt);
                MessageBox.Show("导出结束");
            }
        }

        private void button同一母件同时包含子件_Click(object sender, EventArgs e)
        {
            //将文本框按行分割
            string[] InvCodes = textBox1.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            if (InvCodes.Length == 0)
            {
                MessageBox.Show("请输入子件编码,多个编码请分多行");
                return;
            }
            DataTable dt = 同一母件同时包含子件();
            导出(dt);
            
        }


      private DataTable  同一母件同时包含子件()
        {
            string[] InvCodes = textBox1.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            string sql = $@"select a.Version 版本号,a.VersionDesc 版本说明,c.InvCode as '母件编码',d.cInvName as '母件名称',d.cInvStd 母件规格,zj.cInvCode 子件编码,
                    zj.cInvStd 子件规格,f.OpComponentId ,f.componentId,f.BaseQtyN /f.BaseQtyD 用量,view_采购入库单价.iUnitCost 子件单价
                    from bom_bom as a
                    join bom_parent as b on a.BomId=b.BomId
                    join bas_part as c on b.ParentId=c.PartId
                    join Inventory as d on c.InvCode=d.cInvCode
                    join ComputationUnit as e ON d.cComUnitCode =e.cComUnitCode
                    join bom_opcomponent f on f.BomId = b.BomId
                    join bas_part zi on zi.PartId = f.componentId
                    join Inventory  as zj on zi.InvCode=zj.cInvCode
                    left join view_采购入库单价 on view_采购入库单价.cInvCode = zj.cInvCode
                    where 1=1
                    and a.BomId IN (
                        SELECT f.BomId
                        FROM bom_opcomponent f
                        JOIN bas_part zi ON zi.PartId = f.componentId
                        JOIN Inventory zj ON zi.InvCode = zj.cInvCode
                        WHERE zj.cInvCode IN ({string.Join(",", InvCodes.Select(x => $"'{x}'"))})
                        GROUP BY f.BomId
                        HAVING COUNT(DISTINCT zj.cInvCode) = {InvCodes.Length}
                    )
                    order by c.InvCode";
            DataTable dt = pub.U8GetTable(sql);
            return dt;
        }

        private void button修改BOM_Click(object sender, EventArgs e)
        {
            Form改BOM FROM = new Form改BOM();
            FROM.ShowDialog();
        }
    }
}
